/*
 * MySqlAPI.cpp
 *
 *  Created on: 2014-12-23
 *      Author: root
 */
#include"PublicConfig.h"
#if USE_PUBLIC_MYSQL
#include"mysql.h"
#include"PublicTool.h"
#include"PublicLog.h"
#include"PublicCry.h"
#include"PublicThread.h"
#include"PublicMySql.h"
#ifdef __MINGW32__
#include<winsock.h>
#else
#include<sys/socket.h>
#endif
//用于连接加锁
static PublicLock m_mysqllock;
//显示mysql密码
static int bShowMySqlPass=0;
//显示运行时SQL
static int bShowRunningSql=0;
typedef struct _MYSQL_BASE_CONF{
MySQLConf conf;
u_char bSet;     //是否设置
char   Descr[32];//描述信息
}MYSQL_BASE_CONF;
//配置文件推荐风格
//HOSTXXX=...
//USERXXX=...
//PASSWDXXX=...
//PORTXXX=...
//DATABASEXXX=...
#define MYSQL_DEFAULT_HOST "127.0.0.1"
#define MYSQL_DEFAULT_USER "admin"
#define MYSQL_DEFAULT_PASS "123456"
#define MYSQL_DEFAULT_PORT 3306
#define MYSQL_DEFALUT_DB   "test"
#if USE_MYSQL_FLAG_ACTIVE
static MYSQL_BASE_CONF MySql_conf[MYSQL_NUM]={
    {{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"主库"},
    {{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"上传库"},
    {{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"备份库"}
};
#elif USE_MYSQL_FLAG_SWITCH
static MYSQL_BASE_CONF MySql_conf[MYSQL_NUM]={
    {{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"主库报警库"},
    {{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"流量库"},
    {{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"交换机上传库"},
    {{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"交换机上传备库"}
};
#elif USE_MYSQL_FLAG_COUNT
static MYSQL_BASE_CONF MySql_conf[MYSQL_NUM]={
    {{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"EM主库"},
    {{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"EM5min数据库1"},
    {{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"EM5min数据库2"},
    {{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"机房带宽库"}
};
#else
static MYSQL_BASE_CONF MySql_conf[MYSQL_NUM]={
	{{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"主库"},
	{{MYSQL_DEFAULT_HOST,"",MYSQL_DEFAULT_USER,MYSQL_DEFAULT_PASS,MYSQL_DEFAULT_PORT,MYSQL_DEFALUT_DB},0,"备库"}
};
#endif
MYSQL*OpenMySQLCommon(MySQLConf conf,const char*Descr){
#ifndef __MINGW32__	
	mysql_thread_init();
#endif
	MYSQL*mysql=mysql_init(NULL);
	time_t starttime=time(NULL);
	do{
//设置超时选项
		int timeout=5;
		mysql_options(mysql,MYSQL_OPT_CONNECT_TIMEOUT,(const char*)&timeout);
		timeout=10;
//实际超时为该值的三倍
		mysql_options(mysql,MYSQL_OPT_READ_TIMEOUT,(const char*)&timeout);
		mysql_options(mysql,MYSQL_OPT_WRITE_TIMEOUT,(const char*)&timeout);
		mysql_options(mysql,MYSQL_SET_CHARSET_NAME,"utf8");
//设置重连
		timeout = 1;
		mysql_options(mysql,MYSQL_OPT_RECONNECT,(const char*)&timeout);
	}while(0);	
	m_mysqllock.Lock();
	MYSQL*mysqlresult=mysql_real_connect(mysql,conf.host,conf.user,conf.passwd,conf.db,conf.port,NULL,CLIENT_MULTI_STATEMENTS);	
	m_mysqllock.UnLock();
//如果主库连接失败,且配置备点，尝试连接备点
	if(mysqlresult==NULL){
		if(bShowMySqlPass){
			WRONG("Connect %s(%s:%u@%s:%s)Errno:%u Error:%s UseTime:%ld s",Descr,
			conf.host,conf.port,conf.user,conf.passwd,mysql_errno(mysql),mysql_error(mysql),time(NULL)-starttime);
		}else{
			WRONG("Connect %s(%s:%u)Errno:%u Error:%s UseTime:%ld s",Descr,
			conf.host,conf.port,mysql_errno(mysql),mysql_error(mysql),time(NULL)-starttime);
        }
		if(conf.hostbak[0]!='\0'){
			m_mysqllock.Lock();
			mysqlresult=mysql_real_connect(mysql,conf.hostbak,conf.user,conf.passwd,conf.db,conf.port,NULL,CLIENT_MULTI_STATEMENTS);
			m_mysqllock.UnLock();
			if(mysqlresult==NULL){
				if(bShowMySqlPass){
					WRONG("ConnectBak %s(%s:%u@%s:%s)Errno:%u Error:%s UseTime:%ld s",Descr,
					conf.hostbak,conf.port,conf.user,conf.passwd,mysql_errno(mysql),mysql_error(mysql),time(NULL)-starttime);
				}else{
					WRONG("ConnectBak %s(%s:%u)Errno:%u Error:%s UseTime:%ld s",Descr,
					conf.hostbak,conf.port,mysql_errno(mysql),mysql_error(mysql),time(NULL)-starttime);
				}
			}else{
				INFOEX("ConnectBak %s(%s:%u)OK UseTime:%ld s",Descr,
				conf.hostbak,conf.port,time(NULL)-starttime);
			}
		}
	}
//连接成功进行其它配置	
	if(mysqlresult!=NULL){
#if 1
//防止大量TIME_WAIT
		struct linger so_linger={1,0};
		setsockopt(mysql->net.fd,SOL_SOCKET,SO_LINGER,(const char*)&so_linger,sizeof(struct linger));
//不等的话还是会有TIME_WAIT
		usleep_s(1000);
#endif
//增加默认	group_concat 长度	
		const char*defaultconf="SET SESSION group_concat_max_len=5120;";
		RunMySQL(mysql,defaultconf,0,NULL);
	}else{	
		mysql=CloseMySQL(mysql);
    }
	return mysql;
}
void InitMySQL(){
	mysql_library_init(0,NULL,NULL);
}
void UnInitMySQL(){
	mysql_library_end();
}
MYSQL*OpenMySQL(int type){
	if(MySql_conf[type].bSet){
		return OpenMySQLCommon(MySql_conf[type].conf,MySql_conf[type].Descr);
	}else{
		return NULL;
	}
	return NULL;
}

int CheckMySQLAlive(int type){
	MYSQL*mysql=OpenMySQL(type);
	if(mysql==NULL){
		return -1;
	}
	mysql=CloseMySQL(mysql);
	return 0;
}

int RunMySQL(MYSQL*mysql,const char*sqlbuff,unsigned int check,const char*checksql){
    if(mysql==NULL)return -1;
	if(mysql==NULL||sqlbuff==NULL){
		WRONG("ErrRunMySql ISNULL:%d ISNULL:%d",mysql==NULL,sqlbuff==NULL);
		return -1;
	}
	time_t start=time(NULL);
	int ret=mysql_query(mysql,sqlbuff);
	time_t usetime=time(NULL)-start;
	if(ret!=0){
		if((mysql_errno(mysql)==check)&&(checksql!=NULL)){
			unsigned int saveerrno=mysql_errno(mysql);
			char saveerr[1024]={0};
			strncpy(saveerr,mysql_error(mysql),sizeof(saveerr)-1);
			if(mysql_query(mysql,checksql)==0){
				return RunMySQL(mysql,sqlbuff);
			}else{
				WRONG("errno:%u error:%s usetime:%ld",saveerrno,saveerr,usetime);
			}
		}else{
			WRONG("errno:%u error:%s usetime:%ld",mysql_errno(mysql),mysql_error(mysql),usetime);
		}
//2006 MySQL server has gone away
//2013 Lost connection to MySQL server during query
		if(mysql_errno(mysql)==2006||mysql_errno(mysql)==2013){
			if(mysql_ping(mysql)==0){
				WRONG("尝试重连成功");
//重连成功时延长等待时长
				int timeout=20;
				mysql_options(mysql,MYSQL_OPT_READ_TIMEOUT,(const char*)&timeout);
				mysql_options(mysql,MYSQL_OPT_WRITE_TIMEOUT,(const char*)&timeout);
				start=time(NULL);
				ret=mysql_query(mysql,sqlbuff);
				usetime=time(NULL)-start;
				if(ret!=0)WRONG("errno:%u error:%s usetime:%ld",mysql_errno(mysql),mysql_error(mysql),usetime);
			}else{
				WRONG("尝试重连失败");
			}
		}else{
			WRONG("Wrong SQL:%s",sqlbuff);
		}
	}else
	if(bShowRunningSql){
		INFOEX("Running OK SQL:%s",sqlbuff);
	}
	return ret;
}
MYSQL_RES*RunMySQLGetRes(MYSQL*mysql,const char*sqlbuff,my_ulonglong&num){
	num=0;
    if(RunMySQL(mysql,sqlbuff)!=0)return NULL;
	MYSQL_RES*res=mysql_store_result(mysql);
	if(res) num=mysql_num_rows(res);
    return res;
}
int SelectMySQLDB(MYSQL*mysql,const char*db,int bCreate){
    if(mysql==NULL)return-1;
    if(mysql_select_db(mysql,db)!=0){
//连接丢失导致
		if(mysql_errno(mysql)==2006){
			if(mysql_ping(mysql)==0){
				WRONG("尝试重连成功");
				if(mysql_select_db(mysql,db)==0)
					return 0;
			}else{
				WRONG("尝试重连失败");
            }
        }
//1049 Unknown database 'XXXX'
		if((mysql_errno(mysql)==1049)&&(bCreate==1)){
			char buff[128]={0};
			sprintf(buff,"CREATE DATABASE `%s`;",db);
			RunMySQL(mysql,buff);
			if(mysql_select_db(mysql,db)!=0){
				WRONG("切换数据库:%s 失败 errno:%u error:%s",db,mysql_errno(mysql),mysql_error(mysql));
				return -1;
            }
			return 0;
        }
		WRONG("切换数据库:%s 失败 errno:%u error:%s",db,mysql_errno(mysql),mysql_error(mysql));
		return -1;
    }
    return 0;
}

//将src中单引号,斜杠前前增加斜杠
int ReplaceMySqlStr(const char*src,char*dst,int len){
	int  i=0;
	int  j=0;
	while(src[i]!='\0'){
		if(src[i]=='\''||src[i]=='\\'){
			dst[j++]='\\';
		}
		dst[j++]=src[i++];
		if(j>len){
			return -1;
			break;
		}
	}
	return 0;
}

void ShowMySQLRes(MYSQL_RES*res){
	if(res==NULL)return;
    uint32_t fields=mysql_num_fields(res);
    uint64_t rows=mysql_num_rows(res);
    INFO("结果集列数:%u 行数:" PRU64,fields,rows);
    MYSQL_FIELD*field=mysql_fetch_field(res);
    std::string fieldstr="";
    while(field!=NULL){
//		INFO("%s",field->org_name);
		fieldstr+=field->org_name;
		fieldstr+="\t";
		field=mysql_fetch_field(res);
    }
    INFO("%s",fieldstr.c_str());
    MYSQL_ROW row=mysql_fetch_row(res);
    while(row!=NULL){
		uint32_t i=0;
		std::string rowstr="";
		while(i<fields){
			if(row[i])
				rowstr+=row[i];
			else
				rowstr+="(NULL)";
			rowstr+="\t";
			i++;
        }
		INFO("%s",rowstr.c_str());
		row=mysql_fetch_row(res);
    }
}
MYSQL_ROW MySQLFetchRow(MYSQL_RES*res){
	return mysql_fetch_row(res);
}
MYSQL_RES*MySQLStoreResult(MYSQL*mysql){
	return mysql_store_result(mysql);
}
void MySQLDataSeek(MYSQL_RES*res,int pos){
	mysql_data_seek(res,pos);
}
MYSQL_RES*FreeMySQLRes(MYSQL_RES*res){
    if(res)mysql_free_result(res);
    return NULL;
}
my_ulonglong MySQLNumRows(MYSQL_RES*res){
	return mysql_num_rows(res);
}
unsigned long MySQLThreadID(MYSQL*mysql){
	if(mysql==NULL)return 0;
	return mysql_thread_id(mysql);
}
int MySQLErrno(MYSQL*mysql){
	return mysql_errno(mysql);
}
const char*MySQLError(MYSQL*mysql){
	return mysql_error(mysql);
}
int MySQLPing(MYSQL*mysql){
	return mysql_ping(mysql);
}
MYSQL*CloseMySQL(MYSQL*mysql){
	if(mysql!=NULL){
		mysql_close(mysql);
#ifndef __MINGW32__			
		mysql_thread_end();
#endif		
	}
	return NULL;
}
void SetMySQLConf(MySQLConf conf,int type,int bDec){
//需要解密且解密失败退出	
	if(bDec&&(!DECRYPT((u_char*)conf.passwd,strlen(conf.passwd)))){
		printf("pass dec failed type:%d\n",type);
		exit(0);
	}
	MySql_conf[type].conf=conf;
	MySql_conf[type].bSet=1;
}
MySQLConf GetMySQLConf(int type){
	return MySql_conf[type].conf;
}
void UpDateMySQLConf(const char*IP,const char*db,const char*descr,int type){
	if(IP!=NULL){
		strcpy(MySql_conf[type].conf.host,IP);
		MySql_conf[type].bSet=1;
	}
	if(db!=NULL)strcpy(MySql_conf[type].conf.db,db);
	if(descr!=NULL)strcpy(MySql_conf[type].Descr,descr);
}
void SetMySQLShowRunningSQL(int bShow){
	bShowRunningSql=bShow;
}
void ShowMySQLConf(){
	int i=0;
	for(i=0;i<MYSQL_NUM;i++){
		if(MySql_conf[i].bSet){
			MySQLConf Ex_conf=MySql_conf[i].conf;
			if(bShowMySqlPass){
				printf("%s:%s:%d@%s:%s DB:%s\n",MySql_conf[i].Descr,
				Ex_conf.host,Ex_conf.port,Ex_conf.user,Ex_conf.passwd,Ex_conf.db);
			}else{
				printf("%s:%s:%d DB:%s\n",MySql_conf[i].Descr,
				Ex_conf.host,Ex_conf.port,Ex_conf.db);
			}
		}else{
			printf("未设置%s数据库\n",MySql_conf[i].Descr);
		}
	}
	printf("Show Running MySQL:%d\n",bShowRunningSql);
}
int IsMySQLConfAlive(int type){
    if(type<0||type>=MYSQL_NUM)
       return -1;
    return MySql_conf[type].bSet;
}
int CheckSQLConf(MySQLConf conf){
	if((strlen(conf.host)>0)&&
		(strlen(conf.user)>0)&&
		(strlen(conf.passwd)>0)&&
		(strlen(conf.db)>0)&&
		(conf.port>0)){
		return 0;
	}
	return -1;
}
int MySQLReadConfig(MySQLConf*conf,const char*key,const char*val){
	return -1;
}
#endif
